// $codepro.audit.disable packageNamingConvention, com.instantiations.assist.eclipse.analysis.audit.rule.effectivejava.alwaysOverridetoString.alwaysOverrideToString

/** Import Packages */
package edu.gatech.team404.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.gatech.team404.Account;
import edu.gatech.team404.AccountStatus;
import edu.gatech.team404.AccountType;
import edu.gatech.team404.tool.Log;

/**
 * A Data Access Layer for AccountTable.
 * @author Michael Dandy
 * @version 1.0
 */
public class AccountDAL extends IDatabase {
	
	/** Class tag for logging purpose */
	private final String TAG = "AccountDAL";
	
	/** The table name that this DAL is associated with */
	private final String TABLE_NAME = "AccountTable";
	
	/** The team table that this DAL is associated with */
	private final String TEAM_TABLE = "TeamTable";
	
	/** The grade table that this DAL is associated with */
	private final String GRADE_TABLE = "GradeTable";
	
	/** The student table that this DAL is associated with */
	private final String STUDENT_TABLE = "StudentTable";

	/**
	 * Add an account to the database.
	 * @param acct Account to be added
	 * @return true on success or false otherwise
	 */
	public boolean addAccount(Account acct) {
		final String query = "INSERT INTO " + TABLE_NAME + " VALUES (" 
								+ "\'" + acct.getUsername() + "\', " 
								+ "\'" + acct.getPassword() + "\', " 
								+ "\'" + acct.getName() + "\', " 
								+ "\'" + acct.getEmail() + "\', "
								+ "\'" + acct.getType().toString() + "\', "
								+ "\'" + acct.getStatus().toString() + "\')";
		Log.info(TAG, "Adding account ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Parse account type.
	 * @param type the type of an account
	 * @return the type of an account
	 */
	private AccountType parseType(String type) {
		if (type.equalsIgnoreCase("Admin")) {
			return AccountType.ADMIN;
		}
		else if (type.equalsIgnoreCase("Instructor")) {
			return AccountType.INSTRUCTOR;
		}
		else if (type.equalsIgnoreCase("Student")) {
			return AccountType.STUDENT;
		}
		else {
			return AccountType.UNKNOWN;
		}
	}
	
	/**
	 * Parse account status.
	 * @param status the status of an account
	 * @return the status of an account
	 */
	private AccountStatus parseStatus(String status) {
		if (status.equalsIgnoreCase("Active")) {
			return AccountStatus.ACTIVE;
		}
		else if (status.equalsIgnoreCase("Suspended")) {
			return AccountStatus.SUSPENDED;
		}
		else {
			return AccountStatus.UNKNOWN;
		}
	}
	
	/**
	 * Retrieve an account information associated with the given username.
	 * @param username the username of an account
	 * @return true on success or false otherwise
	 */
	public Account getAccount(String username) {
		final String query = "SELECT * FROM " + TABLE_NAME 
							+ " WHERE Username = \'" + username + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving account ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					final Account acct = new Account (result.getString(1), 
												result.getString(2), 
												result.getString(3), result.getString(4), 
												parseType(result.getString(5)), 
												parseStatus(result.getString(6)));
					return acct;
				}
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Retrieve all accounts stored in the database.
	 * @return all accounts stored in the database
	 */
	public List<Account> getAllAccounts() {
		final String query = "SELECT * FROM " + TABLE_NAME;
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving all accounts ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Account> accts = new ArrayList<Account>();
				while(result.next()) {
					accts.add(new Account (result.getString(1), result.getString(2), 
										   result.getString(3), result.getString(4), 
										   parseType(result.getString(5)), 
										   parseStatus(result.getString(6))));
				}
				return accts;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Retrieve all instructor accounts in the database.
	 * @return all instructor accounts in the database
	 */
	public List<Account> getAllInstructors() {
		final String query = "SELECT * FROM " + TABLE_NAME 
							+ " WHERE Type=\'" + AccountType.INSTRUCTOR.toString() + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving all instructor accounts ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Account> accts = new ArrayList<Account>();
				while(result.next()) {
					accts.add(new Account (result.getString(1), result.getString(2), 
										   result.getString(3), result.getString(4), 
										   parseType(result.getString(5)), 
										   parseStatus(result.getString(6))));
				}
				return accts;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Replace an existing account with the new account. Only password, name, 
	 * and email can be changed.
	 * @param acct the updated account
	 * @return true on success or false otherwise
	 */
	public boolean updateAccount(Account acct) {
		String query = "UPDATE " + TABLE_NAME + " SET ";
		String update = "";
		final Account old = getAccount(acct.getUsername());
		
		if (!acct.getPassword().isEmpty() && 
			!old.getPassword().equalsIgnoreCase(acct.getPassword())) {
			update += "Password=\'" + acct.getPassword() + "\'";
		}
		if (!acct.getName().isEmpty() && 
			!old.getName().equalsIgnoreCase(acct.getName())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "Name=\'" + acct.getName() + "\'";
		}
		if (!acct.getEmail().isEmpty() && 
			!old.getEmail().equalsIgnoreCase(acct.getEmail())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "Email=\'" + acct.getEmail() + "\'";
		}
		
		if (!update.isEmpty()) {
			query += update;
			query += " WHERE Username=\'" + acct.getUsername() + "\'";
			
			Log.info(TAG, "Updating account ...");
			Log.info(TAG, query);
			return super.executeUpdate(query);
		}
		
		return true;
	}
	
	/**
	 * Remove an account from the database.
	 * @param username the username of an account
	 * @return true on success or false otherwise
	 */
	public boolean removeAccount(String username) {
		boolean ret = true;
		
		//Delete from Account Table
		String query = "DELETE FROM " + TABLE_NAME 
							+ " WHERE Username=\'" + username + "\'";
		
		Log.info(TAG, "Removing account ...");
		Log.info(TAG, query);
		ret |= super.executeUpdate(query);
		
		//Delete from TeamTable
		query = "DELETE FROM " + TEAM_TABLE 
				+ " WHERE Member=\'" + username + "\'";

		Log.info(TAG, "Removing account from team ...");
		Log.info(TAG, query);
		ret |= super.executeUpdate(query);
		
		//Delete from GradeTable
		query = "DELETE FROM " + GRADE_TABLE
				+ " WHERE Username=\'" + username + "\'";
		
		Log.info(TAG, "Removing account from grade ...");
		Log.info(TAG, query);
		ret |= super.executeUpdate(query);
		
		//Delete from StudentTable
		query = "DELETE FROM " + STUDENT_TABLE 
				+ " WHERE Username=\'" + username + "\'";
		
		Log.info(TAG, "Removing account from student table ...");
		Log.info(TAG, query);
		ret |= super.executeUpdate(query);
		
		return ret;
	}
	
	/**
	 * Suspend an account with the given username.
	 * @param username the username of an account
	 * @return true on success or false otherwise
	 */
	public boolean suspend(String username) {
		final String query = "UPDATE " + TABLE_NAME 
							+ " SET Status=\'" 
							+ AccountStatus.SUSPENDED.toString() + "\' "
							+ "WHERE Username=\'" + username + "\'";
		
		Log.info(TAG, "Suspending account ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Unsuspend an account with the given username.
	 * @param username the username of an account
	 * @return true on success or false otherwise
	 */
	public boolean unsuspend(String username) {
		final String query = "UPDATE " + TABLE_NAME 
							+ " SET Status=\'" + AccountStatus.ACTIVE.toString() + "\' "
							+ "WHERE Username=\'" + username + "\'";
		
		Log.info(TAG, "Unsuspending account ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Close SQL connection
	 * @return true on success or false otherwise
	 */
	public boolean close() {
		return super.close();
	}
}
